#include "sqlhelper.h"
#include <QDebug>

namespace MISystem
{

    SqlHelper::SqlHelper() 
    {
        if (!QSqlDatabase::contains(_dbPath)) 
        {
            if (!initDatabase()) {
                qDebug() << "数据库初始化失败:" << getLastError();
            }
        }
        else
        {
            _db = QSqlDatabase::database(_dbPath);
        }
    }

    SqlHelper::~SqlHelper()
    {
        closeDatabase();
    }

    bool SqlHelper::initDatabase()
    {
        _db = QSqlDatabase::addDatabase("QSQLITE");
        _db.setDatabaseName(_dbPath);

        if (!_db.open()) {
            _lastError = _db.lastError().text();
            return false;
        }

        return createTables();
    }

    void SqlHelper::closeDatabase()
    {
        if (_db.isOpen()) {
            _db.close();
        }
    }

    bool SqlHelper::createTables()
    {
        QSqlQuery query;

        QString createMaterialTable = R"(
            CREATE TABLE IF NOT EXISTS material_stock (
                material_id VARCHAR(50) PRIMARY KEY,
                material_name VARCHAR(100) NOT NULL,
                stock_quantity INT NOT NULL DEFAULT 0,
                location VARCHAR(100),
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP); )";

        if (!query.exec(createMaterialTable)) {
            _lastError = query.lastError().text();
            return false;
        }

        QString createOperationTable = R"(
            CREATE TABLE IF NOT EXISTS stock_operation (
                operation_id INTEGER PRIMARY KEY AUTOINCREMENT, 
                material_id VARCHAR(50) NOT NULL, 
                operation_type VARCHAR(10) NOT NULL, 
                quantity INT NOT NULL, 
                operator VARCHAR(50) NOT NULL, 
                operation_time DATETIME DEFAULT CURRENT_TIMESTAMP, 
                FOREIGN KEY (material_id) REFERENCES material_stock(material_id)); )";

        if (!query.exec(createOperationTable)) {
            _lastError = query.lastError().text();
            return false;
        }

        return true;
    }

    bool SqlHelper::addMaterial(const MaterialStock& material)
    {
        QSqlQuery query;
        query.prepare("INSERT INTO material_stock (material_id, material_name, stock_quantity, location, create_time) "
            "VALUES (:id, :name, :quantity, :location, :createTime)");
        query.bindValue(":id", material.getMaterialId());
        query.bindValue(":name", material.getMaterialName());
        query.bindValue(":quantity", material.getStockQuantity());
        query.bindValue(":location", material.getLocation());
        query.bindValue(":createTime", QDateTime::currentDateTime().toString(Qt::ISODate));

        if (!query.exec()) {
            _lastError = query.lastError().text();
            return false;
        }
        return true;
    }

    bool SqlHelper::updateMaterial(const MaterialStock& material)
    {
        QSqlQuery query;
        query.prepare("UPDATE material_stock SET material_name = :name, stock_quantity = :quantity, "
            "location = :location WHERE material_id = :id");
        query.bindValue(":name", material.getMaterialName());
        query.bindValue(":quantity", material.getStockQuantity());
        query.bindValue(":location", material.getLocation());
        query.bindValue(":id", material.getMaterialId());

        if (!query.exec()) {
            _lastError = query.lastError().text();
            return false;
        }
        return true;
    }

    bool SqlHelper::deleteMaterial(const QString& materialId)
    {
        QSqlQuery query;
        query.prepare("DELETE FROM material_stock WHERE material_id = ?");
        query.addBindValue(materialId);

        if (!query.exec()) {
            _lastError = query.lastError().text();
            return false;
        }
        return true;
    }

    MaterialStock SqlHelper::getMaterialById(const QString& materialId)
    {
        QSqlQuery query;
        query.prepare("SELECT * FROM material_stock WHERE material_id = ?");
        query.addBindValue(materialId);

        if (query.exec() && query.next()) {
            return MaterialStock(
                query.value("material_id").toString(),
                query.value("material_name").toString(),
                query.value("stock_quantity").toInt(),
                query.value("location").toString(),
                query.value("create_time").toDateTime()
            );
        }
        return MaterialStock();
    }

    QList<MaterialStock> SqlHelper::getAllMaterials()
    {
        QList<MaterialStock> materials;
        QSqlQuery query("SELECT * FROM material_stock ORDER BY material_id");

        while (query.next()) {
            materials.append(MaterialStock(
                query.value("material_id").toString(),
                query.value("material_name").toString(),
                query.value("stock_quantity").toInt(),
                query.value("location").toString(),
                query.value("create_time").toDateTime()
            ));
        }
        return materials;
    }

    bool SqlHelper::materialExists(const QString& materialId)
    {
        QSqlQuery query;
        query.prepare("SELECT 1 FROM material_stock WHERE material_id = ?");
        query.addBindValue(materialId);
        return query.exec() && query.next();
    }

    // 库存操作记录
    bool SqlHelper::addStockOperation(const StockOperation& operation)
    {
        QSqlQuery query;
        query.prepare("INSERT INTO stock_operation (material_id, operation_type, quantity, operator) "
            "VALUES (:materialId, :type, :quantity, :operator)");
        query.bindValue(":materialId", operation.getMaterialId());
        query.bindValue(":type", operation.getOperationTypeString());
        query.bindValue(":quantity", operation.getQuantity());
        query.bindValue(":operator", operation.getOperator());

        if (!query.exec()) {
            _lastError = query.lastError().text();
            return false;
        }
        return true;
    }

    QList<StockOperation> SqlHelper::getOperationsByMaterialId(const QString& materialId)
    {
        QList<StockOperation> operations;
        QSqlQuery query;
        query.prepare("SELECT * FROM stock_operation WHERE material_id = ? ORDER BY operation_time DESC");
        query.addBindValue(materialId);

        if (query.exec()) {
            while (query.next()) {
                StockOperation::OpType type = query.value("operation_type").toString() == "IN"
                    ? StockOperation::In : StockOperation::Out;

                operations.append(StockOperation(
                    query.value("operation_id").toInt(),
                    query.value("material_id").toString(),
                    type,
                    query.value("quantity").toInt(),
                    query.value("operator").toString(),
                    query.value("operation_time").toDateTime()
                ));
            }
        }
        return operations;
    }

    QList<StockOperation> SqlHelper::getAllOperations()
    {
        QList<StockOperation> operations;
        QSqlQuery query("SELECT * FROM stock_operation ORDER BY operation_time DESC");

        while (query.next()) {
            StockOperation::OpType type = query.value("operation_type").toString() == "IN"
                ? StockOperation::In : StockOperation::Out;

            operations.append(StockOperation(
                query.value("operation_id").toInt(),
                query.value("material_id").toString(),
                type,
                query.value("quantity").toInt(),
                query.value("operator").toString(),
                query.value("operation_time").toDateTime()
            ));
        }
        return operations;
    }

    QList<StockOperation> SqlHelper::getOperationsByDateRange(const QDateTime& start, const QDateTime& end)
    {
        QList<StockOperation> operations;
        QSqlQuery query;
        query.prepare("SELECT * FROM stock_operation WHERE operation_time BETWEEN ? AND ? ORDER BY operation_time DESC");
        query.addBindValue(start);
        query.addBindValue(end);

        if (query.exec()) {
            while (query.next()) {
                StockOperation::OpType type = query.value("operation_type").toString() == "IN"
                    ? StockOperation::In : StockOperation::Out;

                operations.append(StockOperation(
                    query.value("operation_id").toInt(),
                    query.value("material_id").toString(),
                    type,
                    query.value("quantity").toInt(),
                    query.value("operator").toString(),
                    query.value("operation_time").toDateTime()
                ));
            }
        }
        return operations;
    }

    // 库存操作
    bool SqlHelper::stockIn(const QString& materialId, int quantity, const QString& operatorName)
    {
        _db.transaction();

        if (!updateMaterialQuantity(materialId, quantity)) {
            _db.rollback();
            return false;
        }

        // 添加操作记录
        StockOperation operation(0, materialId, StockOperation::In, quantity, operatorName, QDateTime::currentDateTime());
        if (!addStockOperation(operation)) {
            _db.rollback();
            return false;
        }

        _db.commit();
        return true;
    }

    bool SqlHelper::stockOut(const QString& materialId, int quantity, const QString& operatorName)
    {
        if (quantity <= 0) {
            _lastError = "出库数量必须大于0";
            return false;
        }

        // 检查库存是否足够
        MaterialStock material = getMaterialById(materialId);
        if (material.getStockQuantity() < quantity) {
            _lastError = "库存不足";
            return false;
        }

        _db.transaction();

        // 更新库存数量
        if (!updateMaterialQuantity(materialId, -quantity)) {
            _db.rollback();
            return false;
        }

        // 添加操作记录
        StockOperation stock(0, materialId, StockOperation::Out, quantity, operatorName, QDateTime::currentDateTime());
        if (!addStockOperation(stock)) {
            _db.rollback();
            return false;
        }

        _db.commit();
        return true;
    }

    bool SqlHelper::updateMaterialQuantity(const QString& materialId, int quantityChange)
    {
        QSqlQuery query;
        query.prepare("UPDATE material_stock SET stock_quantity = stock_quantity + ? WHERE material_id = ?");
        query.addBindValue(quantityChange);
        query.addBindValue(materialId);

        if (!query.exec()) {
            _lastError = query.lastError().text();
            return false;
        }
        return true;
    }

    QString SqlHelper::getLastError() const
    {
        return _lastError;
    }

}
